﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using LRS.DTO;

namespace LRS.DAL
{
    public static class dEmployee
    {
        public static string InsertAdmin(eEmployee employee)
        {
            InsertEmployee(employee);
            SqlDataReader reader = SqlHelper.ExecuteReader("Select top 1 EmployeeNumber from Employee Order By EmployeeID DESC");
            reader.Read();
            string username = reader[0].ToString();
            reader.Close();
            return username;
        }
        //Insert new Employee
        public static void InsertEmployee(eEmployee employee)
        {
            List<SqlParameter> Parameters = new List<SqlParameter>();
            Parameters.Add(new SqlParameter("@FirstName", employee.FirstName));
            Parameters.Add(new SqlParameter("@LastName", employee.LastName));
            Parameters.Add(new SqlParameter("@Gender", employee.Gender.ToString()));
            Parameters.Add(new SqlParameter("@Address", employee.Address));
            Parameters.Add(new SqlParameter("@DateOfBirth", employee.DateOfBirth));
            Parameters.Add(new SqlParameter("@Phone", employee.Phone));
            Parameters.Add(new SqlParameter("@Email", employee.Email));
            Parameters.Add(new SqlParameter("@OtherInfo", employee.OtherInfo));
            string salt = HashingPassword.CreatePasswordSalt(10);
            Parameters.Add(new SqlParameter("@PasswordSalt", salt));
            Parameters.Add(new SqlParameter("@PasswordHash", HashingPassword.CreatePasswordHash(employee.Password, salt)));
            Parameters.Add(new SqlParameter("@Type", employee.Type));
            SqlHelper.ExecuteNonQuery("Insert into Employee(FirstName, LastName, Gender, Address, DateOfBirth, Phone, Email, OtherInfo, PasswordSalt, PasswordHash, Type) values(@FirstName, @LastName, @Gender, @Address, @DateOfBirth, @Phone, @Email, @OtherInfo, @PasswordSalt, @PasswordHash, @Type)", Parameters);
        }

        public static void UpdateEmployee(eEmployee employee)
        {
            List<SqlParameter> Parameters = new List<SqlParameter>();
            Parameters.Add(new SqlParameter("@EmployeeID", employee.EmployeeID));
            Parameters.Add(new SqlParameter("@FirstName", employee.FirstName));
            Parameters.Add(new SqlParameter("@LastName", employee.LastName));
            Parameters.Add(new SqlParameter("@Gender", employee.Gender.ToString()));
            Parameters.Add(new SqlParameter("@Address", employee.Address));
            Parameters.Add(new SqlParameter("@DateOfBirth", employee.DateOfBirth));
            Parameters.Add(new SqlParameter("@Phone", employee.Phone));
            Parameters.Add(new SqlParameter("@Email", employee.Email));
            Parameters.Add(new SqlParameter("@OtherInfo", employee.OtherInfo));
            Parameters.Add(new SqlParameter("@Type", employee.Type));
            SqlHelper.ExecuteNonQuery("Update Employee set FirstName=@FirstName, LastName=@LastName, Gender=@Gender, Address=@Address, DateOfBirth=@DateOfBirth, Phone=@Phone, Email=@Email, OtherInfo=@OtherInfo, Type=@Type WHERE EmployeeID=@EmployeeID", Parameters);
        }

        public static void DeleteEmployeeByID(int EmpID)
        {
            SqlHelper.ExecuteNonQuery("Delete from Employee where EmployeeID=" + EmpID);
        }
        /// <summary>
        /// Check whether a employee with given username and password exists, return null if there is not 
        /// 
        /// </summary>
        /// <param name="Username"></param>
        /// <param name="Password"></param>
        /// <returns></returns>
        public static eEmployee GetEmployeeByUsernameAndPassword(string Username, string Password)
        {
            SqlDataReader reader = SqlHelper.ExecuteReader("Select * from Employee where EmployeeNumber='" + Username + "'");
            reader.Read();
            if (!reader.HasRows)
            {
                reader.Close();
                return null;
            }
            else
            {
                string pwhash = HashingPassword.CreatePasswordHash(Password, reader["PasswordSalt"].ToString());
                if (pwhash == reader["PasswordHash"].ToString())
                {
                    eEmployee emp = new eEmployee();
                    emp.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
                    emp.EmployeeNumber = reader["EmployeeNumber"].ToString();
                    emp.FirstName = reader["FirstName"].ToString();
                    emp.LastName = reader["LastName"].ToString();
                    emp.Gender = Boolean.Parse(reader["Gender"].ToString());
                    emp.Address = reader["Address"].ToString();
                    emp.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
                    emp.Phone = reader["Phone"].ToString();
                    emp.Email = reader["Email"].ToString();
                    emp.OtherInfo = reader["OtherInfo"].ToString();
                    emp.Password = reader["PasswordHash"].ToString();
                    emp.Type = reader["Type"].ToString();
                    reader.Close();
                    return emp;
                }
            }
            reader.Close();
            return null;
        }

        public static List<eEmployee> GetEmployees()
        {
            List<eEmployee> list = new List<eEmployee>();
            SqlDataReader reader = SqlHelper.ExecuteReader("Select * from Employee");
            while (reader.Read())
            {
                eEmployee emp = new eEmployee();
                emp.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
                emp.EmployeeNumber = reader["EmployeeNumber"].ToString();
                emp.FirstName = reader["FirstName"].ToString();
                emp.LastName = reader["LastName"].ToString();
                emp.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
                emp.Gender = Boolean.Parse(reader["Gender"].ToString());
                emp.Address = reader["Address"].ToString();
                emp.Phone = reader["Phone"].ToString();
                emp.Email = reader["Email"].ToString();
                emp.OtherInfo = reader["OtherInfo"].ToString();
                emp.Password = reader["PasswordHash"].ToString();
                emp.Type = reader["Type"].ToString();
                list.Add(emp);
            }
            reader.Close();
            return list;
        }
        public static List<eEmployee> GetEmployeesByKeyword(string keyword)
        {
            List<eEmployee> list = new List<eEmployee>();
            SqlDataReader reader = SqlHelper.ExecuteReader("Select * from Employee e WHERE e.EmployeeNumber like '%' + " + keyword + " + '%' OR e.FirstName like '%' + " + keyword + " + '%' OR e.LastName like '%' + " + keyword + " + '%'");
            while (reader.Read())
            {
                eEmployee emp = new eEmployee();
                emp.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
                emp.EmployeeNumber = reader["EmployeeNumber"].ToString();
                emp.FirstName = reader["FirstName"].ToString();
                emp.LastName = reader["LastName"].ToString();
                emp.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
                emp.Gender = Boolean.Parse(reader["Gender"].ToString());
                emp.Address = reader["Address"].ToString();
                emp.Phone = reader["Phone"].ToString();
                emp.Email = reader["Email"].ToString();
                emp.OtherInfo = reader["OtherInfo"].ToString();
                emp.Password = reader["PasswordHash"].ToString();
                emp.Type = reader["Type"].ToString();
                list.Add(emp);
            }
            reader.Close();
            return list;
        }
        public static int GetNumberOfEmployee()
        {
            SqlDataReader reader = SqlHelper.ExecuteReader("Select count(*) from Employee");
            reader.Read();
            int count = Int32.Parse(reader[0].ToString());
            reader.Close();
            return count;
        }
        public static eEmployee GetEmployeeByID(int EmployeeID)
        {
            SqlDataReader reader = SqlHelper.ExecuteReader("Select * from Employee where EmployeeID=" + EmployeeID);
            reader.Read();
            if (!reader.HasRows)
            {
                reader.Close();
                return null;
            }
            eEmployee emp = new eEmployee();
            emp.EmployeeID = Int32.Parse(reader["EmployeeID"].ToString());
            emp.EmployeeNumber = reader["EmployeeNumber"].ToString();
            emp.FirstName = reader["FirstName"].ToString();
            emp.LastName = reader["LastName"].ToString();
            emp.DateOfBirth = DateTime.Parse(reader["DateOfBirth"].ToString());
            emp.Gender = Boolean.Parse(reader["Gender"].ToString());
            emp.Address = reader["Address"].ToString();
            emp.Phone = reader["Phone"].ToString();
            emp.Email = reader["Email"].ToString();
            emp.OtherInfo = reader["OtherInfo"].ToString();
            emp.Password = reader["PasswordHash"].ToString();
            emp.Type = reader["Type"].ToString();
            reader.Close();
            return emp;
        }
        public static void UpdatePassword(int EmployeeID, string Password)
        {
            SqlDataReader reader = SqlHelper.ExecuteReader("Select PasswordSalt from Employee where EmployeeID=" + EmployeeID);
            if (reader.Read())
            {
                string PasswordSalt = reader["PasswordSalt"].ToString();
                reader.Close();
                string PasswordHash = HashingPassword.CreatePasswordHash(Password, PasswordSalt);
                SqlHelper.ExecuteNonQuery("Update Employee set PasswordHash='" + PasswordHash + "' where EmployeeID=" + EmployeeID);
            }
        }
    }
}
